package com.lc.demo.mapper;

import com.lc.demo.bean.Score;
import com.lc.demo.bean.Student;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * @ClassName StudentMapper
 * @Deacription TODO
 * @Author jinshuhan
 * @Version 1.0
 **/

@Mapper
public interface StudentMapper {

    @Select("select * from student where  username=#{username} and password=#{password}")
    Student selectStudentByUsernameAndPaassword(@Param("username") String stuId, @Param("password") String stuPass);


    @Select("select * from score where  username=#{username} and status in (1,2)")
    List<Score> getScoreListByUsername(String username);
    @Select("select * from student where  username=#{username} ")
    Student selectByUsername(String username);

    @Insert("insert into student  values(#{username},#{password},#{name},#{sex},#{nation},#{card},#{school},#{education} ,#{major} ,#{courts},#{classid} ,#{classname} ,#{status} ,#{phone})")
    void addStudent(Student student);

    @Delete("delete from student where username=#{username}")
    void delStudentByUsername(String username);
    @Select("select * from student ")
    List<Student> getStudentList();

    @Update("update student set password=#{password}, name=#{name},sex=#{sex},nation=#{nation},card=#{card},school=#{school},education=#{education},major=#{major}," +
            "courts=#{courts},classname=#{classname},classid=#{classid},status=#{status},phone=#{phone} where username=#{username}")
    void updateStudent(Student student);


    @Select("SELECT avg( t1.score ) AS avgscore,t1.username, t2.classname FROM score t1 LEFT JOIN student t2 ON t1.username = t2.username where t2.classname=#{classname} and t2.major=#{major} GROUP BY t1.username,t2.classname ORDER BY avg( t1.score ) DESC")
    List<Student> classrank(Student student);

    @Select("SELECT avg( t1.score ) AS avgscore,t1.username, t2.major FROM score t1 LEFT JOIN student t2 ON t1.username = t2.username where t2.major=#{major} GROUP BY t1.username,t2.major ORDER BY avg( t1.score ) DESC")
    List<Student> graderank(Student student);

/*
    @Select("select * from student where  stu_id=#{stuId} and stu_pass=#{stuPass}")
     Student selectStudentByIdAndPass(@Param("stuId") String stuId, @Param("stuPass") String stuPass);

    @Select("select * from student")
    List<Student> selectAllStudent();

    @Insert("insert into student (stu_id,stu_name,stu_class,stu_sex,stu_tele) values" +
            "(#{stuId},#{stuName},#{stuClass},#{stuSex},#{stuTele})")
     int insertStudent(Student stu);

    @Insert("insert into student  values(#{stuId},#{stuName},#{stuPass},#{stuClass},#{stuSex},#{stuTele})")
    int insertStudentHavaPass(Student stu);

    @Select("select * from student where  stu_id=#{stuId} ")
    Student selectStudentByStuId(String stuId);

    @Delete("DELETE from student where stu_id=#{stuId}")
    int deleteStuById(String StuId);

    @Select(value = "SELECT * from student where stu_class=#{sutClass}")
    List<Student> selectStuByStuclass(String sutClass);

    @Select("select stu_name from student where stu_id=#{stuId} ")
    String selectNameById(String stuId);

    @Select("select stu_id from student where stu_class=#{stuClass}")
    List<String> selectIdByClass(String stuClass);*/
}
